package com.swordget.dao.impl;

import com.alibaba.druid.pool.DruidDataSource;
import com.swordget.dao.DataVerifyDao;
import com.swordget.entity.po.DataVerifyPo;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

/**
 * @Author: David
 * @Date: Created in 18:18 2020/4/9
 * @Description:
 * @Modified By:
 * @Version:
 */
@Repository
public class DataVerifyDaoImpl implements DataVerifyDao {

    @Autowired
    private DruidDataSource dataSource;

    /**
     * DataVerifyDaoImpl log
     */
    private final static Logger LOG = LoggerFactory.getLogger(DataVerifyDaoImpl.class);

    @Override
    public DataVerifyPo verify(String tableName, String checkCommand, String columns, String values, String keyValue) {
        final String tmpTableName = tableName;
        checkCommand = checkCommand.replaceAll(tableName, tmpTableName);
        String insertCommand = String.format("INSERT INTO %s (%s) VALUES(%s)", tmpTableName,
                columns.toString(), values.toString());
        //TODO: SQL 语句可能性能不好，后期优化
        checkCommand = String.format("SELECT t.* FROM (%s) t WHERE SERIALNO = '%s'", checkCommand, keyValue);
        //删除临时表插入的数据
        String deleteSQL = String.format("DELETE FROM %s WHERE SERIALNO = '%s'", tmpTableName, keyValue);
        //start insert
        Connection connection = null;
        Statement statement= null;
        ResultSet resultSet = null;
        try{
            connection = dataSource.getConnection();
            statement = connection.createStatement();
            //插入到临时表
            if (statement.executeUpdate(insertCommand) <= 0) {
                throw new RuntimeException("无法向临时表中添加数据");
            }
            //执行校验语句
            resultSet = statement.executeQuery(checkCommand);
            if (resultSet.next()) {
                return DataVerifyPo.builder()
                        .mark(resultSet.getString("MARK"))
                        .reason(resultSet.getString("REASON"))
                        .build();
            }
        } catch (SQLException e) {
            LOG.error("程序执行过程中出现异常", e);
            throw new RuntimeException("校验过程中出现异常!");
        } finally {
            if (null != connection && null != statement) {
                try {
                    statement.executeUpdate(deleteSQL);
                } catch (SQLException e) {
                    LOG.error("程序执行过程中出现异常", e);
                }
            }
            if (null != resultSet) {
                try {
                    resultSet.close();
                } catch (SQLException e) {
                    LOG.error("程序执行过程中出现异常", e);
                }
            }

            if (null != statement) {
                try {
                    statement.close();
                } catch (SQLException e) {
                    LOG.error("程序执行过程中出现异常", e);
                }
            }

            if (null != connection) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    LOG.error("程序执行过程中出现异常", e);
                }
            }
        }
        return null;
    }

    @Override
    public List<DataVerifyPo> verify(String tableName, String tableNameTmp, String verifyCommand, String importId) {
        //替换正式表名
        verifyCommand = verifyCommand.replaceAll(tableName, tableNameTmp);
        String checkCommand = String.format("SELECT a.* FROM (" +
                "(%s) a INNER JOIN " +
                "(SELECT SERIALNO FROM %s WHERE IMPORTID = '%s') b on a.SERIALNO = b. SERIALNO)", verifyCommand, tableNameTmp, importId);
        try (Connection conn = dataSource.getConnection();
            Statement statement = conn.createStatement();
            ResultSet resultSet = statement.executeQuery(checkCommand)) {
            List<DataVerifyPo> verifyPos = new ArrayList<>();
            while (resultSet.next()) {
                verifyPos.add(DataVerifyPo.builder()
                        .mark(resultSet.getString("MARK"))
                        .reason(resultSet.getString("REASON"))
                        .build());
            }
            return verifyPos;
        } catch (SQLException e) {
            LOG.error("程序执行过程中出现异常", e);
            throw new RuntimeException("校验过程中出现异常", e);
        }
    }
}
